﻿CREATE PROCEDURE [RestartFramework].[PutETLJobStagePerJobName]
		@pETLJobName			SYSNAME
,		@pETLJobStageName		SYSNAME
,		@pETLJobStageOrder		SMALLINT    =   NULL
,		@pETLJobStageEnabled	BIT			=	1
,		@pUpdateIfExists		BIT			=	1
,		@pETLJobStageId			SMALLINT	=   NULL			OUTPUT
AS
BEGIN
		SET NOCOUNT ON;
		BEGIN TRY
				DECLARE @vETLJobId  INT =   (   SELECT  ej.[ETLJobId]
												FROM    [RestartFramework].[ETLJob] ej
												WHERE   ej.[ETLJobName] = @pETLJobName
											);
				IF  (@vETLJobID IS NULL)
						SET     @pETLJobStageId = NULL;
				ELSE
						EXEC    [RestartFramework].[PutETLJobStage]		
											@pETLJobId              =   @vETLJobId
									,		@pETLJobStageName       =   @pETLJobStageName
									,		@pETLJobStageOrder      =   @pETLJobStageOrder
									,		@pETLJobStageEnabled    =   @pETLJobStageEnabled
									,		@pUpdateIfExists		=	@pUpdateIfExists
									,		@pETLJobStageId         =   @pETLJobStageId         OUTPUT;
		END TRY
		BEGIN CATCH
				SET	@pETLJobStageId = NULL;
				THROW;                
		END CATCH
END
